package control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import util.Pagination;
import util.StringFormat;

import model.News;
import util.DateControl;
import dbconn.DBConnect;

/*************
 * 新闻管理类
 * 增删改查
 * 更新点击次数
 * 分页显示
 * @author Cyoung
 *date 05/12/2012
 */
public class NewsControl {
	public static Pagination pageInstance = null;
	
	
	public static boolean addNews(News n){
		String addtime = DateControl.Date(); 
		DBConnect dbc = new DBConnect();
		String sql = "insert into news(title,content,keyword,subcolumn,addtime)values(?,?,?,?,?)";
		dbc.prepareStatement(sql);
		try {
			int index = 1;
			dbc.setString(index++, n.getTitle());
			dbc.setString(index++, n.getContent());
			dbc.setString(index++, n.getKeyword());
			dbc.setInt(index++, n.getSubcolumn());
			dbc.setString(index++, addtime);
			dbc.executeUpdate();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
	}
	
	public  static News getOneNews(int id) {
	    DBConnect dbc = new DBConnect();
	    News n = null;
	    String sql = "select * from news where id='"+id+"'";
	    dbc.prepareStatement(sql);
	    try {
		ResultSet rSet= dbc.executeQuery();
		if(rSet.next()){
		    n = new News();
		    n.setAddtime(rSet.getString("addtime"));
		    n.setSubcolumn(rSet.getInt("subcolumn"));
		    n.setContent(rSet.getString("content"));
		    n.setId(rSet.getInt("id"));
	            n.setKeyword(rSet.getString("keyword"));
		    n.setTitle(rSet.getString("title"));
	            n.setView(rSet.getInt("view"));
		}
		return n;
	    } catch (Exception e) {
		// TODO: handle exception
		e.printStackTrace();
		return n;
	    }finally{
		dbc.close();
	    }
	}
	
	public static boolean addView(int id){
		DBConnect dbc = new DBConnect();
		String sql = "update news set view = view + 1 where id = ?";
//		System.out.println(sql);
		dbc.prepareStatement(sql);
		try{
			dbc.setInt(1, id);
			dbc.executeUpdate();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}	
	}
	public static boolean deleteNews(int id){
		DBConnect dbc = new DBConnect();
		String sql = "delete from news where id='"+id+"'";
		dbc.prepareStatement(sql);
		try {
			dbc.executeUpdate();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
		    e.printStackTrace();
		    return false;
		}finally{
			dbc.close();
		}
	}
	
	public static boolean updateNews(News n,int id){
		String addtime = DateControl.Date(); 
		DBConnect dbc = new DBConnect();
		String sql = "update news set title=?,content=?,keyword=?,subcolumn=?,addtime=? where id=?";
		dbc.prepareStatement(sql);
		try {
			int index = 1;
			dbc.setString(index++, n.getTitle());
			dbc.setString(index++, n.getContent());
			dbc.setString(index++, n.getKeyword());
			dbc.setInt(index++, n.getSubcolumn());
			dbc.setString(index++, addtime);
			dbc.setInt(index++, id);
			dbc.executeUpdate();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
	}
	
	//获取指定数量的新闻
	public static List<News> getSomeNews(int num) {
	    List<News> news = new ArrayList<News>();
	    DBConnect dbc = new DBConnect();
	    String sql = "select * from news order by addtime desc  limit "+num+"";
	    dbc.prepareStatement(sql);
	    System.out.println(sql);
	    try {
		ResultSet rSet = dbc.executeQuery();
		while(rSet.next()){
		        News n = new News();
			n.setAddtime(rSet.getString("addtime"));
			n.setSubcolumn(rSet.getInt("subcolumn"));
			n.setContent(rSet.getString("content"));
			n.setId(rSet.getInt("id"));
			n.setKeyword(rSet.getString("keyword"));
			n.setTitle(rSet.getString("title"));
			n.setView(rSet.getInt("view"));
			news.add(n);
		}
		return news;
	    } catch (Exception e) {
		// TODO: handle exception
		e.printStackTrace();
		return news;
	    }finally{
		dbc.close();
	    }
	}
	
	//获取部分内容（用于作为导航显示新闻内容）
	public static String  getBrief(String str,int num) {
	    str = StringFormat.RemoveFormat(str);
	    if(str.length()>num){
		    str = str.substring(0, num)+"...";
	    }else{
	      	str+="...";
	    }
	    return str;
	}
	
	
	//关键字搜索
	public Collection Search(int page,int rowsPerPage,String keyword) throws SQLException, Exception
	{
	    String sql = "select * from news where keyword like '%"+keyword+"%' order by addtime desc ";	
	    System.out.println(sql);
		pageInstance = new NewsControlPage(sql, rowsPerPage);
		return pageInstance.getPage(page);
	}

	//分类新闻
	public Collection getNewsBySubColumn(int page,int rowsPerPage,int sid) throws SQLException, Exception
	{
	    String sql = "select * from news where subcolumn = '"+sid+"' order by addtime desc ";	
	    System.out.println(sql);
	    pageInstance = new NewsControlPage(sql, rowsPerPage);
	    return pageInstance.getPage(page);
	}
	
	
	class NewsControlPage extends Pagination {
	public NewsControlPage(String sql, int rowsPerPage) {
		super(sql, rowsPerPage, 1);
	}
	protected Collection<News> packResultSet(ResultSet rSet) throws SQLException {
		Collection<News> NewsList = new ArrayList<News>();
		while (rSet.next()) {
			News n = new News();
			n.setAddtime(rSet.getString("addtime"));
			n.setSubcolumn(rSet.getInt("subcolumn"));
			n.setContent(rSet.getString("content"));
			n.setId(rSet.getInt("id"));
			n.setKeyword(rSet.getString("keyword"));
			n.setTitle(rSet.getString("title"));
			n.setView(rSet.getInt("view"));
			NewsList.add(n);
			n=null;
		}
		return NewsList;
	}
  }
}
